import pymysql
from pymysql.cursors import DictCursor
from contextlib import contextmanager
import hashlib
import os
import bcrypt
import redis
import json
import logging
import asyncio

# 设置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# 数据库连接配置
DB_CONFIG = {
    'host': '150.109.6.243',
    'user': 'music_ocr_db',
    'password': 'wZktkPy4X2TpZcCY',
    'db': 'music_ocr_db',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor,
}

# Redis配置
REDIS_CONFIG = {
    'host': 'localhost',
    'port': 6379,
    'db': 0,
}

# 异步数据库操作的实现
async def fetch_all(query, params=None):
    """异步执行查询并返回所有结果"""
    loop = asyncio.get_event_loop()
    try:
        def _execute_query():
            with get_db_cursor() as cursor:
                cursor.execute(query, params)
                return cursor.fetchall()
        
        # 在线程池中执行数据库操作
        return await loop.run_in_executor(None, _execute_query)
    except Exception as e:
        logger.error(f"执行fetch_all查询出错: {str(e)}")
        raise

async def fetch_one(query, params=None):
    """异步执行查询并返回单个结果"""
    loop = asyncio.get_event_loop()
    try:
        def _execute_query():
            with get_db_cursor() as cursor:
                cursor.execute(query, params)
                return cursor.fetchone()
        
        # 在线程池中执行数据库操作
        return await loop.run_in_executor(None, _execute_query)
    except Exception as e:
        logger.error(f"执行fetch_one查询出错: {str(e)}")
        raise

async def execute(query, params=None):
    """异步执行更新、插入或删除操作"""
    loop = asyncio.get_event_loop()
    try:
        def _execute_query():
            with get_db_cursor(commit=True) as cursor:
                cursor.execute(query, params)
                return cursor.lastrowid
        
        # 在线程池中执行数据库操作
        return await loop.run_in_executor(None, _execute_query)
    except Exception as e:
        logger.error(f"执行execute操作出错: {str(e)}")
        raise

@contextmanager
def get_db_connection():
    """创建数据库连接的上下文管理器"""
    connection = pymysql.connect(**DB_CONFIG)
    try:
        yield connection
    finally:
        connection.close()

@contextmanager
def get_db_cursor(commit=False):
    """创建数据库游标的上下文管理器"""
    with get_db_connection() as connection:
        cursor = connection.cursor()
        try:
            yield cursor
            if commit:
                connection.commit()
        except Exception:
            connection.rollback()
            raise
        finally:
            cursor.close()

def hash_password(password):
    """使用bcrypt对密码进行哈希"""
    # 生成盐值并哈希密码
    salt = bcrypt.gensalt()
    hashed = bcrypt.hashpw(password.encode('utf-8'), salt)
    return hashed.decode('utf-8')

def verify_password(plain_password, hashed_password):
    """验证密码是否匹配"""
    return bcrypt.checkpw(
        plain_password.encode('utf-8'), 
        hashed_password.encode('utf-8')
    )

def get_user_by_username(username):
    """通过用户名获取用户信息"""
    with get_db_cursor() as cursor:
        sql = "SELECT * FROM users WHERE username = %s"
        cursor.execute(sql, (username,))
        return cursor.fetchone()

def get_user_by_email(email):
    """通过邮箱获取用户信息"""
    with get_db_cursor() as cursor:
        sql = "SELECT * FROM users WHERE email = %s"
        cursor.execute(sql, (email,))
        return cursor.fetchone()

def create_user(username, email, password, full_name=None, music_identity='hobbyist'):
    """创建新用户"""
    hashed_password = hash_password(password)
    
    with get_db_cursor(commit=True) as cursor:
        # 插入用户数据
        sql = """
            INSERT INTO users (username, email, password_hash, full_name, music_identity)
            VALUES (%s, %s, %s, %s, %s)
        """
        cursor.execute(sql, (username, email, hashed_password, full_name, music_identity))
        user_id = cursor.lastrowid
        
        # 初始化用户统计数据
        stats_sql = """
            INSERT INTO user_stats (user_id, scanned_count, conversation_count, post_count, comment_count)
            VALUES (%s, 0, 0, 0, 0)
        """
        cursor.execute(stats_sql, (user_id,))
        
        return user_id

def add_user_preferences(user_id, preference_type, preference_values):
    """添加用户偏好"""
    with get_db_cursor(commit=True) as cursor:
        for value in preference_values:
            if value:  # 确保值不为空
                sql = """
                    INSERT IGNORE INTO user_preferences (user_id, preference_type, preference_value)
                    VALUES (%s, %s, %s)
                """
                cursor.execute(sql, (user_id, preference_type, value))

def get_user_details(user_id):
    """获取用户详细信息，包括偏好和统计数据"""
    with get_db_cursor() as cursor:
        # 获取用户基本信息
        sql = """
            SELECT 
                u.id, u.username, u.email, u.full_name, u.bio, u.avatar_url, 
                u.music_identity, u.created_at, 
                COALESCE(s.scanned_count, 0) AS scanned_count,
                COALESCE(s.conversation_count, 0) AS conversation_count,
                COALESCE(s.post_count, 0) AS post_count,
                COALESCE(s.comment_count, 0) AS comment_count
            FROM users u
            LEFT JOIN user_stats s ON u.id = s.user_id
            WHERE u.id = %s
        """
        cursor.execute(sql, (user_id,))
        user = cursor.fetchone()
        
        if not user:
            return None
        
        # 获取用户偏好
        pref_sql = """
            SELECT preference_type, preference_value
            FROM user_preferences
            WHERE user_id = %s
        """
        cursor.execute(pref_sql, (user_id,))
        preferences = cursor.fetchall()
        
        # 整理偏好数据
        user['preferences'] = {}
        for pref in preferences:
            pref_type = pref['preference_type']
            pref_value = pref['preference_value']
            
            if pref_type not in user['preferences']:
                user['preferences'][pref_type] = []
            
            user['preferences'][pref_type].append(pref_value)
        
        return user 

def record_score_upload(user_id, original_filename, stored_filename, score_title=None, file_size=None, file_type=None, detection_json=None, original_image_url=None, visualization_url=None, json_url=None):
    """记录乐谱上传信息"""
    print(f"=== 尝试记录乐谱上传 ===")
    print(f"用户ID: {user_id}, 类型: {type(user_id)}")
    print(f"原始文件名: {original_filename}")
    print(f"存储文件名: {stored_filename}")
    print(f"URL信息: 原图URL: {original_image_url}, 可视化URL: {visualization_url}, JSON URL: {json_url}")
    
    with get_db_cursor(commit=True) as cursor:
        # 首先检查表结构
        cursor.execute("SHOW COLUMNS FROM score_uploads")
        columns = [column['Field'] for column in cursor.fetchall()]
        print(f"表列: {columns}")
        
        # 基本列，这些列肯定存在
        base_columns = ['user_id', 'original_filename', 'stored_filename', 'score_title', 'file_size', 'file_type', 'detection_json', 'status']
        base_values = [user_id, original_filename, stored_filename, score_title, file_size, file_type, detection_json, 'completed']
        
        # 检查URL相关列是否存在
        url_columns = []
        url_values = []
        
        if 'original_image_url' in columns and original_image_url:
            url_columns.append('original_image_url')
            url_values.append(original_image_url)
            
        if 'visualization_url' in columns and visualization_url:
            url_columns.append('visualization_url')
            url_values.append(visualization_url)
            
        if 'json_url' in columns and json_url:
            url_columns.append('json_url')
            url_values.append(json_url)
            
        if 'cloud_storage' in columns:
            url_columns.append('cloud_storage')
            url_values.append(True if any([original_image_url, visualization_url, json_url]) else False)
        
        # 构建SQL语句
        all_columns = base_columns + url_columns
        all_values = base_values + url_values
        
        # 生成占位符
        placeholders = ', '.join(['%s'] * len(all_columns))
        column_str = ', '.join(all_columns)
        
        sql = f"""
            INSERT INTO score_uploads 
            ({column_str})
            VALUES ({placeholders})
        """
        print(f"执行SQL: {sql}")
        print(f"参数: {all_values}")
        
        # 执行SQL
        try:
            cursor.execute(sql, all_values)
            # 如果插入成功，还需要更新用户统计数据
            upload_id = cursor.lastrowid
            print(f"插入成功，ID: {upload_id}")
            
            if upload_id:
                try:
                    # 更新用户扫描计数
                    update_sql = """
                        UPDATE user_stats 
                        SET scanned_count = scanned_count + 1
                        WHERE user_id = %s
                    """
                    cursor.execute(update_sql, (user_id,))
                except Exception as e:
                    print(f"更新用户统计信息时出错: {str(e)}")
                    
            return upload_id
        except Exception as e:
            print(f"数据库插入错误: {str(e)}")
            raise

def record_audio_files(score_upload_id, midi_filename, wav_filename, midi_url=None, wav_url=None, duration=None):
    """记录生成的音频文件信息"""
    with get_db_cursor(commit=True) as cursor:
        # 首先检查表结构
        cursor.execute("SHOW COLUMNS FROM audio_files")
        columns = [column['Field'] for column in cursor.fetchall()]
        
        # 基本列，这些列肯定存在
        base_columns = ['score_upload_id', 'midi_filename', 'wav_filename']
        base_values = [score_upload_id, midi_filename, wav_filename]
        
        # 检查URL相关列是否存在
        additional_columns = []
        additional_values = []
        
        if 'midi_url' in columns and midi_url:
            additional_columns.append('midi_url')
            additional_values.append(midi_url)
            
        if 'wav_url' in columns and wav_url:
            additional_columns.append('wav_url')
            additional_values.append(wav_url)
            
        if 'duration' in columns and duration:
            additional_columns.append('duration')
            additional_values.append(duration)
            
        if 'cloud_storage' in columns:
            additional_columns.append('cloud_storage')
            additional_values.append(True if any([midi_url, wav_url]) else False)
        
        # 构建SQL语句
        all_columns = base_columns + additional_columns
        all_values = base_values + additional_values
        
        # 生成占位符
        placeholders = ', '.join(['%s'] * len(all_columns))
        column_str = ', '.join(all_columns)
        
        sql = f"""
            INSERT INTO audio_files 
            ({column_str})
            VALUES ({placeholders})
        """
        
        # 执行SQL
        cursor.execute(sql, all_values)
        return cursor.lastrowid

def record_detection_stats(score_upload_id, total_symbols, note_count, rest_count, key_signature=None, time_signature=None, detected_tempo=None, difficulty_level='intermediate', mood=None):
    """记录识别结果统计信息"""
    with get_db_cursor(commit=True) as cursor:
        sql = """
            INSERT INTO detection_stats 
            (score_upload_id, total_symbols, note_count, rest_count, key_signature, time_signature, detected_tempo, difficulty_level, mood)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(sql, (
            score_upload_id,
            total_symbols,
            note_count,
            rest_count,
            key_signature,
            time_signature,
            detected_tempo,
            difficulty_level,
            mood
        ))
        return cursor.lastrowid

def get_user_uploads(user_id, limit=10, offset=0, include_stats=False):
    """获取用户的乐谱上传历史"""
    with get_db_cursor() as cursor:
        base_sql = """
            SELECT 
                su.id, su.original_filename, su.score_title, su.upload_time, 
                su.file_size, su.file_type, su.status,
                su.original_image_url, su.visualization_url, su.json_url,
                af.midi_url, af.wav_url
            FROM 
                score_uploads su
            LEFT JOIN 
                audio_files af ON su.id = af.score_upload_id
            WHERE 
                su.user_id = %s
            ORDER BY 
                su.upload_time DESC
            LIMIT %s OFFSET %s
        """
        cursor.execute(base_sql, (user_id, limit, offset))
        uploads = cursor.fetchall()
        
        if include_stats and uploads:
            # 获取统计数据
            upload_ids = [u['id'] for u in uploads]
            placeholders = ','.join(['%s'] * len(upload_ids))
            stats_sql = f"""
                SELECT * FROM detection_stats
                WHERE score_upload_id IN ({placeholders})
            """
            cursor.execute(stats_sql, upload_ids)
            stats = cursor.fetchall()
            
            # 将统计数据添加到上传记录中
            stats_by_id = {s['score_upload_id']: s for s in stats}
            for upload in uploads:
                upload['stats'] = stats_by_id.get(upload['id'], {})
        
        return uploads 

def create_conversation(user_id):
    """创建新的对话会话"""
    try:
        # 确保user_id是整数
        user_id = int(user_id)
        
        with get_db_cursor(commit=True) as cursor:
            # 首先检查用户ID是否有效
            check_sql = "SELECT id FROM users WHERE id = %s"
            cursor.execute(check_sql, (user_id,))
            if not cursor.fetchone():
                print(f"用户ID无效: {user_id}")
                return None
                
            # 创建会话
            sql = "INSERT INTO conversations (user_id) VALUES (%s)"
            cursor.execute(sql, (user_id,))
            new_id = cursor.lastrowid
            print(f"创建会话成功，ID: {new_id}")
            return new_id
    except (ValueError, TypeError):
        print(f"无效的用户ID格式: {user_id}")
        return None
    except Exception as e:
        print(f"创建会话失败: {str(e)}")
        return None

def get_conversation(conversation_id):
    """获取指定会话的详细信息"""
    try:
        # 确保conversation_id是整数
        conversation_id = int(conversation_id)
        
        with get_db_cursor() as cursor:
            # 检查chat_messages表结构
            cursor.execute("SHOW COLUMNS FROM chat_messages")
            message_columns = [column['Field'] for column in cursor.fetchall()]
            
            # 确定时间列名
            time_column = 'id'
            if 'created_at' in message_columns:
                time_column = 'created_at'
            
            # 根据列名构建SQL
            first_message_subquery = f"(SELECT content FROM chat_messages WHERE conversation_id = c.id AND role = 'user' ORDER BY {time_column} ASC LIMIT 1)"
            
            sql = f"""
                SELECT c.*, 
                      (SELECT COUNT(*) FROM chat_messages WHERE conversation_id = c.id) as message_count,
                      {first_message_subquery} as first_message
                FROM conversations c
                WHERE c.id = %s
            """
            cursor.execute(sql, (conversation_id,))
            return cursor.fetchone()
    except (ValueError, TypeError):
        print(f"无效的会话ID格式: {conversation_id}")
        return None
    except Exception as e:
        print(f"获取会话信息时出错: {str(e)}")
        return None

def get_user_conversations(user_id, limit=20, offset=0):
    """获取用户的所有会话列表"""
    with get_db_cursor() as cursor:
        # 检查chat_messages表结构
        cursor.execute("SHOW COLUMNS FROM chat_messages")
        message_columns = [column['Field'] for column in cursor.fetchall()]
        
        # 检查conversations表结构
        cursor.execute("SHOW COLUMNS FROM conversations")
        conv_columns = [column['Field'] for column in cursor.fetchall()]
        
        # 确定时间列名
        time_column = 'id'
        if 'created_at' in message_columns:
            time_column = 'created_at'
        
        # 根据列名构建SQL
        last_activity_subquery = f"(SELECT MAX({time_column}) FROM chat_messages WHERE conversation_id = c.id)"
        first_message_subquery = f"(SELECT content FROM chat_messages WHERE conversation_id = c.id AND role = 'user' ORDER BY {time_column} ASC LIMIT 1)"
        
        # 决定排序列
        order_column = "c.id DESC"
        if 'updated_at' in conv_columns:
            order_column = "c.updated_at DESC"
        
        sql = f"""
            SELECT c.*, 
                  (SELECT COUNT(*) FROM chat_messages WHERE conversation_id = c.id) as message_count,
                  {first_message_subquery} as first_message,
                  {last_activity_subquery} as last_activity
            FROM conversations c
            WHERE c.user_id = %s
            ORDER BY {order_column}
            LIMIT %s OFFSET %s
        """
        cursor.execute(sql, (user_id, limit, offset))
        return cursor.fetchall()

def save_message(user_id, conversation_id, role, content):
    """保存聊天消息"""
    with get_db_cursor(commit=True) as cursor:
        try:
            # 首先检查表结构
            cursor.execute("SHOW COLUMNS FROM chat_messages")
            columns = [column['Field'] for column in cursor.fetchall()]
            
            # 准备要插入的列和值
            insert_columns = ['conversation_id', 'role', 'content']
            insert_values = [conversation_id, role, content]
            
            # 如果表中有user_id列，则添加该值
            if 'user_id' in columns:
                insert_columns.append('user_id')
                insert_values.append(user_id)
            
            # 构建SQL语句
            column_str = ', '.join(insert_columns)
            placeholders = ', '.join(['%s'] * len(insert_columns))
            
            sql = f"""
                INSERT INTO chat_messages ({column_str})
                VALUES ({placeholders})
            """
            
            cursor.execute(sql, insert_values)
            return cursor.lastrowid
        except Exception as e:
            print(f"保存消息失败: {str(e)}")
            return None

def get_conversation_messages(conversation_id, limit=50, offset=0):
    """获取会话中的消息列表"""
    with get_db_cursor() as cursor:
        # 首先查询表结构以确认列名
        cursor.execute("SHOW COLUMNS FROM chat_messages")
        columns = [column['Field'] for column in cursor.fetchall()]
        
        # 根据实际列名选择排序字段
        order_column = 'id'  # 默认使用id排序
        if 'created_at' in columns:
            order_column = 'created_at'
        
        sql = f"""
            SELECT * FROM chat_messages
            WHERE conversation_id = %s
            ORDER BY {order_column} ASC
            LIMIT %s OFFSET %s
        """
        cursor.execute(sql, (conversation_id, limit, offset))
        return cursor.fetchall()

def update_user_stat(user_id, stat_field):
    """更新用户统计信息中的指定字段"""
    valid_fields = ['scanned_count', 'conversation_count', 'post_count', 'comment_count']
    if stat_field not in valid_fields:
        return False
        
    with get_db_cursor(commit=True) as cursor:
        try:
            sql = f"""
                UPDATE user_stats 
                SET {stat_field} = {stat_field} + 1
                WHERE user_id = %s
            """
            cursor.execute(sql, (user_id,))
            return True
        except Exception as e:
            print(f"更新用户统计失败: {str(e)}")
            return False

def delete_conversation(conversation_id, user_id):
    """删除指定会话(只能删除自己的会话)"""
    with get_db_cursor(commit=True) as cursor:
        try:
            # 首先验证会话归属
            check_sql = "SELECT user_id FROM conversations WHERE id = %s"
            cursor.execute(check_sql, (conversation_id,))
            result = cursor.fetchone()
            
            if not result or result['user_id'] != user_id:
                return False
                
            # 删除所有关联消息
            delete_messages_sql = "DELETE FROM chat_messages WHERE conversation_id = %s"
            cursor.execute(delete_messages_sql, (conversation_id,))
            
            # 删除会话
            delete_conv_sql = "DELETE FROM conversations WHERE id = %s"
            cursor.execute(delete_conv_sql, (conversation_id,))
            
            return True
        except Exception as e:
            print(f"删除会话失败: {str(e)}")
            return False 

# 社区相关函数
def get_all_posts(limit=20, offset=0, sort_by='latest', user_id=None):
    """获取所有帖子，带排序和分页"""
    print(f"DEBUG - get_all_posts被调用 - 参数: limit={limit}, offset={offset}, sort_by={sort_by}, user_id={user_id}, 类型: {type(user_id)}")
    
    with get_db_cursor() as cursor:
        # 基本查询，获取帖子信息和作者信息
        base_query = """
            SELECT 
                p.id, p.user_id as author_id, p.title, p.content, p.created_at,
                p.score_upload_id, u.username as author_name, u.avatar_url as author_avatar,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                s.original_filename as score_filename, s.score_title,
                s.visualization_url, a.wav_url
        """
        
        # 添加点赞状态查询
        if user_id:
            print(f"DEBUG - 添加点赞状态查询，user_id = {user_id}")
            base_query += f"""
                , IF((SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = %s) > 0, 1, 0) as liked
            """
        else:
            print(f"DEBUG - 用户未登录或user_id为None，不添加点赞状态查询")
        
        # 基本表连接
        base_query += """
            FROM community_posts p
            LEFT JOIN users u ON p.user_id = u.id
            LEFT JOIN score_uploads s ON p.score_upload_id = s.id
            LEFT JOIN audio_files a ON s.id = a.score_upload_id
            WHERE p.post_status = 'active'
        """
        
        # 排序规则
        if sort_by == 'popular':
            order_clause = "ORDER BY like_count DESC, p.created_at DESC"
        else:  # 默认为'latest'
            order_clause = "ORDER BY p.created_at DESC"
        
        # 添加分页
        limit_clause = f"LIMIT {limit} OFFSET {offset}"
        
        # 组合完整查询
        full_query = f"{base_query} {order_clause} {limit_clause}"
        
        # 决定是否添加user_id参数
        if user_id:
            print(f"DEBUG - 执行SQL查询，带user_id参数: {user_id}")
            cursor.execute(full_query, (user_id,))
        else:
            print(f"DEBUG - 执行SQL查询，不带user_id参数")
            cursor.execute(full_query)
            
        posts = cursor.fetchall()
        print(f"DEBUG - 查询结果: 获取到 {len(posts)} 条帖子")
        
        # 输出每个帖子的visualization_url
        for i, post in enumerate(posts):
            print(f"DEBUG - 帖子 #{i+1}: ID={post['id']}, score_upload_id={post.get('score_upload_id')}")
            print(f"DEBUG - 帖子 #{i+1}: visualization_url={post.get('visualization_url')}")
            print(f"DEBUG - 帖子 #{i+1}: wav_url={post.get('wav_url')}")
        
        # 确保liked字段为布尔值
        if user_id:
            for post in posts:
                if 'liked' in post:
                    post['liked'] = bool(post.get('liked', 0))
                    print(f"DEBUG - 帖子ID: {post['id']}, 点赞状态: {post['liked']}, 原始值: {post.get('liked', None)}")
                else:
                    print(f"DEBUG - 帖子ID: {post['id']} 没有liked字段，SQL查询可能有问题")
        else:
            # 当没有用户ID时，所有帖子默认为未点赞状态
            for post in posts:
                post['liked'] = False
                print(f"DEBUG - 无用户ID, 帖子ID: {post['id']}, 设置点赞状态为false")
        
        # 对标题和内容进行截断处理，限制长度
        title_max_length = 50  # 标题最大长度，超过则截断
        content_max_length = 100  # 内容最大长度，超过则截断
        
        for post in posts:
            # 截断过长的标题
            if post.get('title') and len(post['title']) > title_max_length:
                post['title'] = post['title'][:title_max_length] + '...'
                print(f"DEBUG - 帖子ID: {post['id']}, 标题已截断")
            
            # 截断过长的内容
            if post.get('content') and len(post['content']) > content_max_length:
                post['content'] = post['content'][:content_max_length] + '...'
                print(f"DEBUG - 帖子ID: {post['id']}, 内容已截断")
        
        return posts

def get_user_followed_posts(user_id, limit=20, offset=0):
    """获取用户关注的帖子"""
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if has_follows_table:
            # 如果存在user_follows表，查询用户关注的作者的帖子
            sql = """
                SELECT 
                    p.id, p.title, p.content, p.created_at, p.updated_at, 
                    p.view_count, u.username AS author_name, u.id AS author_id,
                    u.avatar_url AS author_avatar, 
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) AS like_count,
                    (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) AS comment_count,
                    s.id AS score_upload_id, s.score_title, s.visualization_url, s.status,
                    a.wav_url, a.midi_url
                FROM community_posts p
                JOIN users u ON p.user_id = u.id
                LEFT JOIN score_uploads s ON p.score_upload_id = s.id
                LEFT JOIN audio_files a ON s.id = a.score_upload_id
                INNER JOIN user_follows uf ON p.user_id = uf.followed_id AND uf.follower_id = %s
                WHERE p.post_status = 'active'
                ORDER BY p.created_at DESC
                LIMIT %s OFFSET %s
            """
            cursor.execute(sql, (user_id, limit, offset))
        else:
            # 如果不存在，则创建表并返回空列表
            try:
                # 创建user_follows表
                create_table_sql = """
                    CREATE TABLE IF NOT EXISTS user_follows (
                        id INT NOT NULL AUTO_INCREMENT,
                        follower_id INT NOT NULL,
                        followed_id INT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (id),
                        UNIQUE KEY unique_follow (follower_id, followed_id),
                        CONSTRAINT fk_follower FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
                        CONSTRAINT fk_followed FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE
                    )
                """
                cursor.execute(create_table_sql)
                print("创建user_follows表成功")
            except Exception as e:
                print(f"创建user_follows表失败: {str(e)}")
            
            # 返回空列表
            return []
        
        posts = cursor.fetchall()
        
        # 对标题和内容进行截断处理，限制长度
        title_max_length = 10  # 标题最大长度，超过则截断
        content_max_length = 50  # 内容最大长度，超过则截断
        
        for post in posts:
            # 截断过长的标题
            if post.get('title') and len(post['title']) > title_max_length:
                post['title'] = post['title'][:title_max_length] + '...'
                print(f"DEBUG - 用户关注帖子ID: {post['id']}, 标题已截断")
            
            # 截断过长的内容
            if post.get('content') and len(post['content']) > content_max_length:
                post['content'] = post['content'][:content_max_length] + '...'
                print(f"DEBUG - 用户关注帖子ID: {post['id']}, 内容已截断")
                
        return posts

def get_post_detail(post_id, user_id=None):
    """获取帖子详情，包括评论"""
    print(f"DEBUG - get_post_detail被调用 - 参数: post_id={post_id}, user_id={user_id}")
    
    with get_db_cursor() as cursor:
        # 获取帖子基本信息
        base_query = """
            SELECT 
                p.id, p.user_id as author_id, p.title, p.content, p.created_at,
                p.score_upload_id, u.username as author, u.avatar_url as author_avatar,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comments,
                s.original_filename as score_filename, s.score_title,
                s.visualization_url, a.wav_url
        """
        
        # 如果提供了用户ID，添加点赞状态查询
        if user_id:
            base_query += f"""
                , IF((SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = %s) > 0, 1, 0) as liked
            """
            
        # 完成查询
        base_query += """
            FROM community_posts p
            LEFT JOIN users u ON p.user_id = u.id
            LEFT JOIN score_uploads s ON p.score_upload_id = s.id
            LEFT JOIN audio_files a ON s.id = a.score_upload_id
            WHERE p.id = %s AND p.post_status = 'active'
        """
        
        if user_id:
            cursor.execute(base_query, (user_id, post_id))
        else:
            cursor.execute(base_query, (post_id,))
            
        post = cursor.fetchone()
        
        if post:
            # 确保liked字段为布尔值
            if user_id and 'liked' in post:
                post['liked'] = bool(post['liked'])
                print(f"DEBUG - 帖子详情ID: {post_id}, 点赞状态: {post['liked']}, 类型: {type(post['liked'])}")
            elif 'liked' not in post:
                post['liked'] = False
                print(f"DEBUG - 帖子详情ID: {post_id}, 未查询到点赞状态，设置为false")
            
            # 获取帖子评论
            comment_query = """
                SELECT 
                    pc.id, pc.content, pc.created_at,
                    u.id as user_id, u.username as author, u.avatar_url as author_avatar
                FROM post_comments pc
                JOIN users u ON pc.user_id = u.id
                WHERE pc.post_id = %s
                ORDER BY pc.created_at DESC
            """
            
            cursor.execute(comment_query, (post_id,))
            post['commentList'] = cursor.fetchall()
        
        return post

def create_post(user_id, title, content, score_upload_id=None):
    """创建新帖子"""
    with get_db_cursor(commit=True) as cursor:
        sql = """
            INSERT INTO community_posts 
            (user_id, title, content, score_upload_id, view_count, post_status)
            VALUES (%s, %s, %s, %s, 0, 'active')
        """
        cursor.execute(sql, (user_id, title, content, score_upload_id))
        post_id = cursor.lastrowid
        
        # 更新用户统计数据
        update_sql = """
            UPDATE user_stats 
            SET post_count = post_count + 1
            WHERE user_id = %s
        """
        cursor.execute(update_sql, (user_id,))
        
        return post_id

def add_post_comment(post_id, user_id, content):
    """添加评论"""
    with get_db_cursor(commit=True) as cursor:
        sql = """
            INSERT INTO post_comments
            (post_id, user_id, content)
            VALUES (%s, %s, %s)
        """
        cursor.execute(sql, (post_id, user_id, content))
        comment_id = cursor.lastrowid
        
        # 更新用户统计数据
        update_sql = """
            UPDATE user_stats 
            SET comment_count = comment_count + 1
            WHERE user_id = %s
        """
        cursor.execute(update_sql, (user_id,))
        
        # 获取评论信息，包括用户名
        select_sql = """
            SELECT 
                pc.id, pc.content, pc.created_at,
                u.id AS user_id, u.username AS author, u.avatar_url
            FROM post_comments pc
            JOIN users u ON pc.user_id = u.id
            WHERE pc.id = %s
        """
        cursor.execute(select_sql, (comment_id,))
        comment = cursor.fetchone()
        
        # 确保返回的created_at是字符串格式，以便前端解析
        if comment and 'created_at' in comment:
            print(f"评论创建时间: {comment['created_at']}, 类型: {type(comment['created_at'])}")
        
        return comment

def toggle_post_like(post_id, user_id):
    """切换帖子点赞状态，同时更新Redis缓存和MySQL"""
    if not user_id:
        return False
    
    # 导入Redis客户端
    from community_routes import redis_client
    
    # 检查点赞状态，优先使用缓存
    is_liked = check_post_like_status(post_id, user_id)
    print(f"DEBUG - toggle_post_like - 当前点赞状态: post_id={post_id}, user_id={user_id}, liked={is_liked}")
    
    try:
        with get_db_cursor(commit=True) as cursor:
            if is_liked:
                # 已点赞，需要取消点赞
                query = """
                    DELETE FROM post_likes
                    WHERE post_id = %s AND user_id = %s
                """
                cursor.execute(query, (post_id, user_id))
                
                # 更新Redis状态和计数
                if redis_client:
                    try:
                        # 设置用户点赞状态为"未点赞"
                        like_key = f"post_like:{post_id}:{user_id}"
                        redis_client.setex(like_key, 3600, "0")
                        
                        # 减少点赞计数
                        count_key = f"post_like_count:{post_id}"
                        # 如果计数器存在，减一
                        if redis_client.exists(count_key):
                            redis_client.decr(count_key)
                    except Exception as e:
                        print(f"更新Redis点赞状态出错: {str(e)}")
                
                return False  # 返回新状态：未点赞
            else:
                # 未点赞，需要添加点赞
                query = """
                    INSERT INTO post_likes (post_id, user_id)
                    VALUES (%s, %s)
                    ON DUPLICATE KEY UPDATE created_at = CURRENT_TIMESTAMP
                """
                cursor.execute(query, (post_id, user_id))
                
                # 更新Redis状态和计数
                if redis_client:
                    try:
                        # 设置用户点赞状态为"已点赞"
                        like_key = f"post_like:{post_id}:{user_id}"
                        redis_client.setex(like_key, 3600, "1")
                        
                        # 增加点赞计数
                        count_key = f"post_like_count:{post_id}"
                        # 如果计数器存在，加一；否则创建计数器并设置当前点赞数
                        if redis_client.exists(count_key):
                            redis_client.incr(count_key)
                        else:
                            # 点赞计数不存在，需要从数据库获取当前点赞数
                            query_count = """
                                SELECT COUNT(*) as count FROM post_likes WHERE post_id = %s
                            """
                            cursor.execute(query_count, (post_id,))
                            result = cursor.fetchone()
                            count = result['count'] if result else 1
                            redis_client.setex(count_key, 3600, str(count))
                    except Exception as e:
                        print(f"更新Redis点赞状态出错: {str(e)}")
                
                return True  # 返回新状态：已点赞
    except Exception as e:
        print(f"切换点赞状态出错: {str(e)}")
        # 发生错误，返回原始状态
        return bool(is_liked)  # 确保返回布尔值

def check_post_like_status(post_id, user_id, redis_client=None):
    """检查用户是否点赞了指定帖子，优先从Redis缓存中查询"""
    if not user_id:
        print(f"无效的用户ID: {user_id}")
        return False
        
    # 转为整数确保类型匹配
    try:
        post_id = int(post_id)
        user_id = int(user_id)
    except (ValueError, TypeError):
        print(f"无效的ID格式: post_id={post_id}, user_id={user_id}")
        return False
    
    print(f"DEBUG - 检查点赞状态 - 帖子ID: {post_id}, 用户ID: {user_id}")
    
    # 如果提供了Redis客户端，尝试从缓存中获取
    if redis_client:
        try:
            like_key = f"post_like:{post_id}:{user_id}"
            cached_status = redis_client.get(like_key)
            
            if cached_status is not None:
                # 从缓存获取到点赞状态
                is_liked = cached_status == "1"
                print(f"DEBUG - 从Redis获取到点赞状态: post_id={post_id}, user_id={user_id}, liked={is_liked}, 原始值={cached_status}")
                return bool(is_liked)  # 强制转换为布尔值
        except Exception as e:
            # Redis操作失败，降级到直接从数据库查询
            print(f"DEBUG - Redis获取点赞状态失败: {str(e)}")
    
    # 从数据库查询点赞状态
    with get_db_cursor() as cursor:
        sql = """
            SELECT IF(COUNT(*) > 0, 1, 0) as liked FROM post_likes
            WHERE post_id = %s AND user_id = %s
        """
        cursor.execute(sql, (post_id, user_id))
        result = cursor.fetchone()
        is_liked = bool(result['liked']) if result else False
        
        print(f"DEBUG - 从数据库获取点赞状态: post_id={post_id}, user_id={user_id}, liked={is_liked}, 原始值={result['liked'] if result else None}")
        
        # 如果提供了Redis客户端，将结果缓存到Redis
        if redis_client:
            try:
                like_key = f"post_like:{post_id}:{user_id}"
                redis_client.setex(like_key, 3600, "1" if is_liked else "0") # 1小时过期
                print(f"DEBUG - 缓存点赞状态到Redis: {like_key}={'1' if is_liked else '0'}")
            except Exception as e:
                # 缓存失败不影响主要功能
                print(f"DEBUG - 缓存点赞状态到Redis失败: {str(e)}")
        
        return bool(is_liked)  # 强制转换为布尔值

def get_post_likes_count(post_id):
    """获取帖子点赞数"""
    with get_db_cursor() as cursor:
        sql = """
            SELECT COUNT(*) AS like_count 
            FROM post_likes
            WHERE post_id = %s
        """
        cursor.execute(sql, (post_id,))
        result = cursor.fetchone()
        return result['like_count'] if result else 0

def delete_post(post_id, user_id):
    """删除帖子（仅标记为删除，不实际删除数据）"""
    with get_db_cursor(commit=True) as cursor:
        # 检查是否是帖子作者或管理员
        check_sql = """
            SELECT user_id FROM community_posts
            WHERE id = %s
        """
        cursor.execute(check_sql, (post_id,))
        post = cursor.fetchone()
        
        if not post:
            return False, "帖子不存在"
        
        # 检查用户权限
        if post['user_id'] != user_id:
            # 检查是否是管理员
            admin_sql = "SELECT is_admin FROM users WHERE id = %s"
            cursor.execute(admin_sql, (user_id,))
            user = cursor.fetchone()
            if not user or not user['is_admin']:
                return False, "没有删除权限"
        
        # 标记为删除状态
        update_sql = """
            UPDATE community_posts
            SET post_status = 'deleted'
            WHERE id = %s
        """
        cursor.execute(update_sql, (post_id,))
        
        return True, "删除成功"

def follow_user(follower_id, followed_id):
    """关注用户"""
    # 不能关注自己
    if follower_id == followed_id:
        return False, "不能关注自己"
    
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            # 创建user_follows表
            create_table_sql = """
                CREATE TABLE IF NOT EXISTS user_follows (
                    id INT NOT NULL AUTO_INCREMENT,
                    follower_id INT NOT NULL,
                    followed_id INT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    UNIQUE KEY unique_follow (follower_id, followed_id),
                    CONSTRAINT fk_follower FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
                    CONSTRAINT fk_followed FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE
                )
            """
            cursor.execute(create_table_sql)
        
        # 检查是否已经关注
        check_sql = """
            SELECT id FROM user_follows
            WHERE follower_id = %s AND followed_id = %s
        """
        cursor.execute(check_sql, (follower_id, followed_id))
        already_follows = cursor.fetchone() is not None
        
        if already_follows:
            return False, "已经关注了该用户"
        
        # 添加关注关系
        try:
            with get_db_cursor(commit=True) as cursor:
                insert_sql = """
                    INSERT INTO user_follows
                    (follower_id, followed_id)
                    VALUES (%s, %s)
                """
                cursor.execute(insert_sql, (follower_id, followed_id))
            return True, "关注成功"
        except Exception as e:
            return False, f"关注失败: {str(e)}"

def unfollow_user(follower_id, followed_id):
    """取消关注用户"""
    with get_db_cursor(commit=True) as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            return False, "没有关注关系表"
        
        # 检查是否已经关注
        check_sql = """
            SELECT id FROM user_follows
            WHERE follower_id = %s AND followed_id = %s
        """
        cursor.execute(check_sql, (follower_id, followed_id))
        follow_record = cursor.fetchone()
        
        if not follow_record:
            return False, "没有关注该用户"
        
        # 删除关注关系
        try:
            delete_sql = """
                DELETE FROM user_follows
                WHERE follower_id = %s AND followed_id = %s
            """
            cursor.execute(delete_sql, (follower_id, followed_id))
            return True, "取消关注成功"
        except Exception as e:
            return False, f"取消关注失败: {str(e)}"

def check_follow_status(follower_id, followed_id):
    """检查是否关注了指定用户"""
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            return False
        
        # 检查是否已经关注
        check_sql = """
            SELECT id FROM user_follows
            WHERE follower_id = %s AND followed_id = %s
        """
        cursor.execute(check_sql, (follower_id, followed_id))
        return cursor.fetchone() is not None

def get_user_followers(user_id):
    """获取用户的关注者列表"""
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            return []
        
        # 获取关注者列表
        sql = """
            SELECT u.id, u.username, u.avatar_url, u.music_identity
            FROM user_follows uf
            JOIN users u ON uf.follower_id = u.id
            WHERE uf.followed_id = %s
        """
        cursor.execute(sql, (user_id,))
        return cursor.fetchall()

def get_user_following(user_id):
    """获取用户正在关注的用户列表"""
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            return []
        
        # 获取正在关注的用户列表
        sql = """
            SELECT u.id, u.username, u.avatar_url, u.music_identity
            FROM user_follows uf
            JOIN users u ON uf.followed_id = u.id
            WHERE uf.follower_id = %s
        """
        cursor.execute(sql, (user_id,))
        return cursor.fetchall()

def get_followed_posts(user_id, limit=20, offset=0, sort_by='mixed'):
    """获取用户关注的人发布的帖子，支持时间和热度混合排序"""
    print(f"DEBUG - get_followed_posts被调用 - 参数: user_id={user_id}, limit={limit}, offset={offset}, sort_by={sort_by}")
    
    with get_db_cursor() as cursor:
        # 检查是否存在user_follows表
        cursor.execute("SHOW TABLES LIKE 'user_follows'")
        has_follows_table = cursor.fetchone() is not None
        
        if not has_follows_table:
            # 如果不存在，则创建表
            try:
                # 创建user_follows表
                create_table_sql = """
                    CREATE TABLE IF NOT EXISTS user_follows (
                        id INT NOT NULL AUTO_INCREMENT,
                        follower_id INT NOT NULL,
                        followed_id INT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (id),
                        UNIQUE KEY unique_follow (follower_id, followed_id),
                        CONSTRAINT fk_follower FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
                        CONSTRAINT fk_followed FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE
                    )
                """
                cursor.execute(create_table_sql)
                print("创建user_follows表成功")
                # 表是新创建的，用户没有关注任何人，返回空列表
                return []
            except Exception as e:
                print(f"创建user_follows表失败: {str(e)}")
                return []
        
        # 基本查询，获取帖子信息、作者信息和用户关系
        base_query = """
            SELECT 
                p.id, p.user_id as author_id, p.title, p.content, p.created_at,
                p.score_upload_id, u.username as author_name, u.avatar_url as author_avatar,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                s.original_filename as score_filename, s.score_title,
                s.visualization_url, a.wav_url,
                IF((SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = %s) > 0, 1, 0) as liked
            FROM community_posts p
            JOIN users u ON p.user_id = u.id
            LEFT JOIN score_uploads s ON p.score_upload_id = s.id
            LEFT JOIN audio_files a ON s.id = a.score_upload_id
            INNER JOIN user_follows uf ON p.user_id = uf.followed_id AND uf.follower_id = %s
            WHERE p.post_status = 'active'
        """
        
        # 设置排序规则
        if sort_by == 'latest':
            order_clause = "ORDER BY p.created_at DESC"
        elif sort_by == 'popular':
            order_clause = "ORDER BY like_count DESC, p.created_at DESC"
        else:  # 混合排序 - 考虑点赞数和时间的因素
            # 创建一个综合分数: 每个赞相当于3小时的时间优势
            order_clause = """
                ORDER BY 
                (like_count * 3 + TIMESTAMPDIFF(HOUR, '2023-01-01', p.created_at)) DESC
            """
        
        # 添加分页
        limit_clause = f"LIMIT {limit} OFFSET {offset}"
        
        # 组合完整查询
        full_query = f"{base_query} {order_clause} {limit_clause}"
        
        # 执行查询，user_id参数需要传递两次：一次用于点赞状态查询，一次用于关注状态
        cursor.execute(full_query, (user_id, user_id))
        posts = cursor.fetchall()
        print(f"DEBUG - 查询结果: 获取到 {len(posts)} 条关注的帖子")
        
        # 确保liked字段为布尔值
        for post in posts:
            if 'liked' in post:
                post['liked'] = bool(post.get('liked', 0))
                print(f"DEBUG - 帖子ID: {post['id']}, 点赞状态: {post['liked']}")
            
            # 添加标记，表示这些是来自关注用户的帖子
            post['isFollowing'] = True
        
        return posts 

def get_user_profile(user_id):
    """获取用户完整个人资料，包括偏好设置"""
    with get_db_cursor() as cursor:
        # 获取用户基本信息
        sql = """
            SELECT 
                id, username, email, full_name, bio, avatar_url, 
                music_identity, created_at, is_admin
            FROM users 
            WHERE id = %s
        """
        cursor.execute(sql, (user_id,))
        user_data = cursor.fetchone()
        
        if not user_data:
            return None
        
        # 获取用户偏好设置
        preferences_sql = """
            SELECT preference_type, preference_value
            FROM user_preferences
            WHERE user_id = %s
        """
        cursor.execute(preferences_sql, (user_id,))
        preferences = cursor.fetchall()
        
        # 处理偏好设置
        preferences_list = []
        if preferences:
            # 将音乐偏好格式化为列表
            for pref in preferences:
                if pref.get('preference_type') == 'genre':
                    preferences_list.append(pref.get('preference_value'))
        
        user_data['preferences'] = preferences_list
        
        # 获取主要乐器
        main_instrument = None
        for pref in preferences:
            if pref.get('preference_type') == 'instrument':
                main_instrument = pref.get('preference_value')
                break
                
        user_data['main_instrument'] = main_instrument
        
        return user_data

def get_user_public_profile(user_id):
    """获取用户的公开个人资料（不包含敏感信息）"""
    with get_db_cursor() as cursor:
        # 获取用户公开信息
        sql = """
            SELECT 
                id, username, full_name, bio, avatar_url, 
                music_identity
            FROM users 
            WHERE id = %s
        """
        cursor.execute(sql, (user_id,))
        user_data = cursor.fetchone()
        
        if not user_data:
            return None
        
        # 获取用户偏好设置
        preferences_sql = """
            SELECT preference_type, preference_value
            FROM user_preferences
            WHERE user_id = %s
        """
        cursor.execute(preferences_sql, (user_id,))
        preferences = cursor.fetchall()
        
        # 处理偏好设置
        preferences_list = []
        if preferences:
            # 将音乐偏好格式化为列表
            for pref in preferences:
                if pref.get('preference_type') == 'genre':
                    preferences_list.append(pref.get('preference_value'))
        
        user_data['preferences'] = preferences_list
        
        # 获取主要乐器
        main_instrument = None
        for pref in preferences:
            if pref.get('preference_type') == 'instrument':
                main_instrument = pref.get('preference_value')
                break
                
        user_data['main_instrument'] = main_instrument
        
        return user_data

def update_user_profile(user_id, update_data):
    """更新用户个人资料"""
    try:
        with get_db_cursor(commit=True) as cursor:
            # 准备用户表更新字段
            user_fields = ['full_name', 'bio', 'music_identity']
            user_updates = {}
            
            # 提取用户表的字段
            for field in user_fields:
                if field in update_data:
                    user_updates[field] = update_data[field]
            
            # 如果有用户表字段需要更新
            if user_updates:
                # 构建SQL更新语句
                set_clause = ", ".join([f"{key} = %s" for key in user_updates])
                values = list(user_updates.values())
                values.append(user_id)  # 用于WHERE条件
                
                update_sql = f"""
                    UPDATE users
                    SET {set_clause}
                    WHERE id = %s
                """
                cursor.execute(update_sql, values)
            
            # 处理乐器偏好更新
            if 'main_instrument' in update_data and update_data['main_instrument']:
                # 先删除所有instrument类型的偏好
                delete_instrument_sql = """
                    DELETE FROM user_preferences 
                    WHERE user_id = %s AND preference_type = 'instrument'
                """
                cursor.execute(delete_instrument_sql, (user_id,))
                
                # 添加新的乐器偏好
                instrument = update_data['main_instrument']
                insert_instrument_sql = """
                    INSERT INTO user_preferences 
                    (user_id, preference_type, preference_value) 
                    VALUES (%s, 'instrument', %s)
                """
                cursor.execute(insert_instrument_sql, (user_id, instrument))
            
            # 处理音乐偏好更新
            if 'preferences' in update_data and update_data['preferences']:
                # 获取当前偏好
                current_prefs_sql = """
                    SELECT preference_value 
                    FROM user_preferences 
                    WHERE user_id = %s AND preference_type = 'genre'
                """
                cursor.execute(current_prefs_sql, (user_id,))
                current_prefs = [row['preference_value'] for row in cursor.fetchall()]
                
                # 要添加的新偏好
                new_prefs = update_data['preferences']
                if isinstance(new_prefs, str):
                    try:
                        import json
                        new_prefs = json.loads(new_prefs)
                    except:
                        new_prefs = [new_prefs]
                
                # 删除已不存在的偏好
                for pref in current_prefs:
                    if pref not in new_prefs:
                        delete_pref_sql = """
                            DELETE FROM user_preferences 
                            WHERE user_id = %s AND preference_type = 'genre' AND preference_value = %s
                        """
                        cursor.execute(delete_pref_sql, (user_id, pref))
                
                # 添加新的偏好
                for pref in new_prefs:
                    if pref not in current_prefs:
                        insert_pref_sql = """
                            INSERT INTO user_preferences 
                            (user_id, preference_type, preference_value) 
                            VALUES (%s, 'genre', %s)
                        """
                        cursor.execute(insert_pref_sql, (user_id, pref))
        
        return True
    except Exception as e:
        print(f"更新用户资料失败: {str(e)}")
        return False

def update_user_avatar(user_id, avatar_url):
    """更新用户头像URL"""
    try:
        with get_db_cursor(commit=True) as cursor:
            update_sql = """
                UPDATE users
                SET avatar_url = %s
                WHERE id = %s
            """
            cursor.execute(update_sql, (avatar_url, user_id))
        return True
    except Exception as e:
        print(f"更新用户头像失败: {str(e)}")
        return False

def get_user_stats(user_id):
    """获取用户活动统计数据"""
    with get_db_cursor() as cursor:
        stats = {
            'scanned': 0,
            'conversations': 0,
            'posts': 0,
            'comments': 0,
            'likes_received': 0,
            'likes_given': 0,
            'followers': 0,
            'following': 0
        }
        
        # 获取乐谱扫描记录数
        cursor.execute("""
            SELECT COUNT(*) as count FROM score_uploads
            WHERE user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['scanned'] = result['count'] if result else 0
        
        # 获取对话数
        cursor.execute("""
            SELECT COUNT(*) as count FROM conversations
            WHERE user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['conversations'] = result['count'] if result else 0
        
        # 获取帖子数
        cursor.execute("""
            SELECT COUNT(*) as count FROM community_posts
            WHERE user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['posts'] = result['count'] if result else 0
        
        # 获取评论数
        cursor.execute("""
            SELECT COUNT(*) as count FROM post_comments
            WHERE user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['comments'] = result['count'] if result else 0
        
        # 获取收到的点赞数
        cursor.execute("""
            SELECT COUNT(*) as count FROM post_likes pl
            JOIN community_posts cp ON pl.post_id = cp.id
            WHERE cp.user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['likes_received'] = result['count'] if result else 0
        
        # 获取给出的点赞数
        cursor.execute("""
            SELECT COUNT(*) as count FROM post_likes
            WHERE user_id = %s
        """, (user_id,))
        result = cursor.fetchone()
        stats['likes_given'] = result['count'] if result else 0
        
        # 获取关注者数量
        try:
            cursor.execute("""
                SELECT COUNT(*) as count FROM user_follows
                WHERE followed_id = %s
            """, (user_id,))
            result = cursor.fetchone()
            stats['followers'] = result['count'] if result else 0
        except:
            # 表可能不存在
            pass
        
        # 获取关注的人数量
        try:
            cursor.execute("""
                SELECT COUNT(*) as count FROM user_follows
                WHERE follower_id = %s
            """, (user_id,))
            result = cursor.fetchone()
            stats['following'] = result['count'] if result else 0
        except:
            # 表可能不存在
            pass
        
        return stats 